﻿Namespace Hukuk.Af

	Module Functions

		Dim PrimBorclari As Recordset
		Dim OdemePlani As New Recordset("donem(s),anapara(m),ceza(m),tur(s),toplam(m),ay(s),odetar(d)")
		Dim Connection As Token
		Public PesinatTarihi As Date


		Function GetDonem(ByVal Tarih As Date) As String
			Return Trim(Tarih.Year) & "/" & ((Tarih.Month - 1) \ 3) + 1
		End Function

		Sub KaydetIsveren(Token As Token, yil As Integer, sno As String, borc As Prim.Borc.Borc, kaykul As String)
			Token.BeginTran()
			Token.ExecSQL("delete from afgenel where yil = @yil and sno = @sno", yil, sno)
			Token.ExecSQL("delete from aftaksit where yil = @yil and sno = @sno", yil, sno)
			Dim Rs As Recordset = Token.OpenSQL("select * from afgenel where 1 = 0")
			Dim kimlik As String = Sigortali.SnoAra(Token, sno)("kimlik")
			Rs.AddNew("yil,sno,kimlik,pesintar,islemtar,kaykul", yil, sno, kimlik, borc.OdemeTarihi, Token.SysTime, kaykul)
			Rs.Save()
			Rs = Token.OpenSQL("select * from aftaksit where 1 = 0")
			For Each Donem In borc.Donemler.Values
				If Donem.Aktif Then
					For Each Ay In Donem.Aylar
						For Each Bordro In Ay.Bordrolar
							Rs.AddNew()
							Rs.SetValues("yil,sno,kimlik,isyerino", yil, sno, kimlik, Bordro.isyeri)
							Rs.SetValues("donem,bordrotar,bordro", Donem.Donem, Bordro.tarih, Bordro.tip)
							Rs.SetValues("anapara,ceza,toplam", Bordro.anapara, Bordro.indceza, Bordro.anapara + Bordro.indceza)
							Rs.SetValues("odetar,tur", Donem.OdemeTarihi, Donem.PesinDurumu)
						Next
					Next
				End If
			Next
			Rs.Save()
			Token.CommitTran()
		End Sub

		Sub KaydetIsyeri(Token As Token, yil As Integer, isyerino As String, borc As Prim.Borc.Borc, kaykul As String)
			Token.BeginTran()
			Token.ExecSQL("delete from afgenel where yil = @yil and sno is null and isyerino = @isyerino", yil, isyerino)
			Token.ExecSQL("delete from aftaksit where yil = @yil and sno is null and isyerino = @isyerino", yil, isyerino)
			Dim Rs As Recordset = Token.OpenSQL("select * from afgenel where 1 = 0")
			Rs.AddNew("yil,isyerino,pesintar,islemtar,kaykul", yil, isyerino, borc.OdemeTarihi, Token.SysTime, kaykul)
			Rs.Save()
			Rs = Token.OpenSQL("select * from aftaksit where 1 = 0")
			For Each Donem In borc.Donemler.Values
				If Donem.Aktif Then
					For Each Ay In Donem.Aylar
						For Each Bordro In Ay.Bordrolar
							If Bordro.aktif Then
								Rs.AddNew()
								Rs.SetValues("yil,isyerino", yil, isyerino)
								Rs.SetValues("donem,bordrotar,bordro", Donem.Donem, Bordro.tarih, Bordro.tip)
								Rs.SetValues("anapara,ceza,toplam", Bordro.anapara, Bordro.indceza, Bordro.anapara + Bordro.indceza)
								Rs.SetValues("odetar,tur", Donem.OdemeTarihi, Donem.PesinDurumu)
								Rs.Save()
							End If
						Next
					Next
				End If
			Next
			Token.CommitTran()
		End Sub

		Sub BordroSil2(Token As Token, yil As Integer, isyerino As String)
			Token.ExecSQL("delete from afbordro where yil = @yil and isyerino = @isyerino", yil, isyerino)
		End Sub

		Sub BordroKaydet2(Token As Token, yil As Integer, isyerino As String, bordrotar As String, bordro As String, pek As Decimal)
			Dim Rs As Recordset = Token.OpenSQL("select * from afbordro where yil = @yil and isyerino = @isyerino", yil, isyerino)
			Rs.AddNew("yil,isyerino,bordrotar,bordro,pek", yil, isyerino, bordrotar, bordro, pek)
			Rs.Save()
		End Sub

		Sub OdemePlaniDegistirSigortali(Token As Token, yil As Integer, sno As Integer, donem As String, odetar As Date)
			Token.ExecSQL("update aftaksit set odetar = @odetar where yil = @yil sno = @sno and donem = @donem", odetar, yil, sno, donem)
		End Sub

		Sub OdemePlaniDegistirIsyeri(Token As Token, yil As Integer, isyerino As String, donem As String, odetar As Date)
			Token.ExecSQL("update aftaksit set odetar = @odetar where yil = @yil sno is null and isyerino = @isyerinp and donem = @donem", odetar, yil, isyerino, donem)
		End Sub

		Function GenelIsverenOku(Token As Token, yil As Integer, sno As Integer) As Recordset
			Return Token.OpenSQL("select * from afgenel where yil = @yil and sno = @sno", yil, sno)
		End Function

		Function GenelIsyeriOku(Token As Token, yil As Integer, isyerino As String) As Recordset
			Return Token.OpenSQL("select * from afgenel where yil = @yil and sno is null and isyerino = @isyerino", yil, isyerino)
		End Function

		Function IsyeriBordroOku(Token As Token, yil As Integer, isyerino As String) As Recordset
			Return Token.OpenSQL("select * from afbordro where yil = @yil and isyerino = @isyerino", yil, isyerino)
		End Function

		Sub IsyeriBordroKaydet(Token As Token, yil As Integer, isyerino As String, list As Grid)
			Token.BeginTran()
			Token.ExecSQL("delete from afbordro where yil = @yil and isyerino = @isyerino", yil, isyerino)
			Dim Rs = Token.OpenSQL("select * from afbordro where yil = @yil and isyerino = @isyerino", yil, isyerino)
			For Each Row As DataGridViewRow In list.Rows
				Rs.AddNew()
				Rs.SetValues("yil,isyerino,bordrotar,bordro,pek", yil, isyerino, Row.Cells("bordrotar").Value, Row.Cells("bordro").Value, Row.Cells("pek").Value)
			Next
			Rs.Save()
			Token.CommitTran()
		End Sub

		Function YararlananlarListesi(Token As Token, yil As Integer) As Recordset
			Q("select c.sno,c.kimlik,b.isyerino,case when a.sno is null then b.isyeriadi else rtrim(c.adi) + ' ' + rtrim(c.soyadi) end as adi,pesintar")
			Q("from afgenel a left join isyerigenel b on a.isyerino = b.isyerino")
			Q("left join sgenel c on a.sno = c.sno")
			Q("where a.yil = @yil")
			Return Token.OpenSQL("*", yil)
		End Function

		Function TaksitIsverenOku(Token As Token, yil As Integer, sno As Integer) As Recordset
			Return Token.OpenSQL("select * from aftaksit where yil = @yil and sno = @sno order by bordrotar,bordro", yil, sno)
		End Function

		Function TaksitIsyeriOku(Token As Token, yil As Integer, isyerino As Integer) As Recordset
			Return Token.OpenSQL("select * from aftaksit where yil = @yil and sno is null and isyerino = @isyerino order by bordrotar,bordro", yil, isyerino)
		End Function

		Sub OdemePlaniSnoOku(Token As Token, yil As Integer, sno As Integer, OdemePlani As DataTable)
			Dim Rs As Recordset = Token.OpenSQL("select donem,sum(anapara),sum(ceza),sum(toplam) from aftaksit where yil = @yil and sno = @sno and tur = 'T' group by donem order by donem", yil, sno)
			OdemePlani.Rows.Clear()
			Do Until Rs.EOF
				OdemePlani.Rows.Add(Rs(0), Rs(1), Rs(2), Rs(3), True)
				Rs.MoveNext()
			Loop
		End Sub

		Sub OdemePlaniSnoSil(Token As Token, yil As Integer, sno As Integer)
			Token.BeginTran()
			Token.OpenSQL("delete from afgenel where yil = @yil and sno = @sno", yil, sno)
			Token.OpenSQL("delete from aftaksit where yil = @yil and sno = @sno", yil, sno)
			Token.CommitTran()
		End Sub

		Sub OdemePlaniIsyeriOku(Token As Token, yil As Integer, isyerino As String, OdemePlani As DataTable)
			Dim Rs As Recordset = Token.OpenSQL("select donem,sum(anapara),sum(ceza),sum(toplam) from aftaksit where yil = @yil and sno is null and isyerino = @isyerino and tur = 'T' group by donem order by donem", yil, isyerino)
			OdemePlani.Rows.Clear()
			Do Until Rs.EOF
				OdemePlani.Rows.Add(Rs(0), Rs(1), Rs(2), Rs(3), True)
				Rs.MoveNext()
			Loop
		End Sub

		Sub OdemePlaniIsyeriSil(Token As Token, yil As Integer, isyerino As String)
			Token.BeginTran()
			Token.OpenSQL("delete from afgenel where yil = @yil and sno is null and isyerino = @isyerino", yil, isyerino)
			Token.OpenSQL("delete from aftaksit where yil = @yil and sno is null and isyerino = @isyerino", yil, isyerino)
			Token.CommitTran()
		End Sub

		Function CalismaDonemleriOku(Token As Token, sno As Integer) As Recordset
			Return Token.OpenSQL("select * from scalisdonem where sno = @sno", sno)
		End Function

		Sub CalismaDonemleriKaydet(Token As Token, sno As Integer, list As DataGridView)
			Token.BeginTran()
			Token.ExecSQL("delete from scalisdonem where sno = @sno", sno)
			Dim kimlik = Sigortali.SnoAra(Token, sno)("kimlik")
			Dim Rs = CalismaDonemleriOku(Token, sno)
			For Each Row As DataGridViewRow In list.Rows
				Rs.AddNew("sno,kimlik,tarih1,tarih2", sno, kimlik, Row.Cells("tarih1").Value, Row.Cells("tarih2").Value)
			Next
			Rs.Save()
			Token.CommitTran()
		End Sub

		Function SanalYatirimOku(Token As Token, sno As Integer) As Recordset
			Return Token.OpenSQL("select * from shesk where sno = @sno and pek = 0 and makbuzno = -1 and kaykul = '0000'", sno)
		End Function

		Sub SanalYatirimSil(Token As Token, sno As Integer)
			Token.ExecSQL("delete from shesk where sno = @sno and pek = 0 and makbuzno = -1 and kaykul = '0000'", sno)
		End Sub

		Sub SanalYatirimYaz(Token As Token, sno As Integer, bordro As String, list As SortedList(Of Date, Integer))
			Token.BeginTran()
			Dim kimlik = Sigortali.SnoAra(Token, sno)("kimlik")
			Dim Rs = Token.OpenSQL("select sno,kimlik,bordro,bordrotar,pek,pogs,makbuzno,kaykul,islemtar from shesk where 1 = 0")
			For Each Item In list
				Rs.AddNew("sno,kimlik,bordro,bordrotar,pek,pogs,makbuzno,kaykul,islemtar", sno, kimlik, bordro, Item.Key, 0, Item.Value, -1, "0000", Token.SysTime)
			Next
			Rs.Save()
			Token.CommitTran()
		End Sub

	End Module
End Namespace
